
''' This demoes how to break the sqlite database and shows what happens
when we set a maximum database size (here, 2Mb) and we intend to insert 
more stuff than it can actually handle '''

import apsw

SIZE_IN_MB = '2'
DATABASE_NAME = ':memory:'

conn = apsw.Connection(DATABASE_NAME)
c = conn.cursor()
c.execute('''begin''')
c.execute('''pragma page_size''')
page_size = long(list(c).pop()[0])

c.execute('''commit''')
max_size_in_bytes = long(SIZE_IN_MB) * 1024l * 1024l # in MegaBytes

result = long(max_size_in_bytes) / long(page_size) # divide and floor


# Create table
c.execute('''begin''')

c.execute("pragma max_page_count = %s" % (result,))

c.execute('''create table if not exists stuff
    (key text, value text)''')
    
'''Indexes are fun because they eat disk space like crazy. 
It will make the test faster'''
c.execute('''create index if not exists idx_stuff on 
    stuff (key)''')

# Save (commit) the changes
c.execute("commit")

num = 0
beforenum = 0
try:
    while 1:
    	beforenum += 1
        c.execute('''begin''')
        ''' 
        Yay, I love the Python synthax. 
        (String * 9000) means repeat String 9000 times :) 
        We will fill the database with 'spam '. Yep. Stuff is full of spam :)
        '''
        c.execute('''insert into stuff values (?,?)''', (num, 'spam ' * 9000))
        c.execute('''commit''')
        num += 1
except(apsw.FullError):
    c.execute('''select * from stuff where key==?''', (num,))
    res = list(c) # Make sure the last entry rolledback
    print 'Full!'
    #print beforenum, " ", num
    #print res
    
#Let's delete one entry and add a smaller one, just to prove a point.
c.execute("begin")
c.execute("delete from stuff where key==?", (num - 1,))
c.execute("commit")
print "Deleted!"

c.execute("begin")
c.execute('''insert into stuff values (?,?)''', (num, 'spam ' * 9000))
c.execute("commit")
print "Inserted!"
